CREATE OR REPLACE PACKAGE "SISTEMACADE"."manejoCitasMedicas"
AS
    FUNCTION existePaciente
    (cedula NUMBER)
    RETURN NUMBER;

    PROCEDURE registrarCita
    (fecha VARCHAR2, horaInicio VARCHAR2, horaFin VARCHAR2, estatus VARCHAR2, detalles VARCHAR2, cedulaPersonal NUMBER, idConsultorio NUMBER, cedulaPaciente NUMBER, nombrePaciente VARCHAR2, apellidoPaciente VARCHAR2, telefonoPaciente NUMBER, fechaNacPac VARCHAR2, rutaFoto VARCHAR2);
    
    FUNCTION existeEM
    (serial NUMBER)
    RETURN BOOLEAN;

    FUNCTION existeFAR
    (serial NUMBER)
    RETURN BOOLEAN;

    PROCEDURE registrarHistorialPaciente
    (idCita NUMBER, numeroSerialFarmaco NUMBER, numeroSerialEm NUMBER, cedulaPaciente NUMBER, detalles VARCHAR2, porcentajeUso NUMBER);

    FUNCTION estaDisponibleDoctor
    (cedula NUMBER, dia VARCHAR2, horaInicio VARCHAR2, horaFin VARCHAR2)
    RETURN BOOLEAN;

    FUNCTION existeTransporte
    (matricula VARCHAR2)
    RETURN BOOLEAN;

    PROCEDURE realizarTraslado
    (fecha VARCHAR2, hora VARCHAR2, matricula VARCHAR2);

    FUNCTION generarFacturaPaciente
    RETURN NUMBER;

    PROCEDURE registrarServicio (idCita NUMBER, idFactura NUMBER, precio FLOAT, descripcion VARCHAR2);

    PROCEDURE pacienteInscrito (cedula NUMBER);

    FUNCTION poseePlan (cedula NUMBER)
    RETURN BOOLEAN;

    PROCEDURE asignarPlanPaciente (cedula NUMBER, idPlan NUMBER, motivo VARCHAR2);

    FUNCTION existePlan (id NUMBER)
    RETURN NUMBER;

    PROCEDURE redireccionarCita (id NUMBER);

END;
/
CREATE OR REPLACE PACKAGE BODY "SISTEMACADE"."manejoCitasMedicas"
AS
  FUNCTION existePaciente
  (cedula NUMBER) RETURN NUMBER 
  IS
    CURSOR c_paciente IS SELECT P.cedulapaciente 
    FROM PACIENTE P WHERE P.cedulapaciente = cedula;

    reg_pac c_paciente%ROWTYPE;
    existe NUMBER := 0;

    BEGIN
        FOR reg_pac IN c_paciente LOOP
         existe := existe + 1;
	    END LOOP;
    
        --DBMS_OUTPUT.put_line('Paciente: '||existe);
        RETURN existe;
    END;

  PROCEDURE registrarCita
  (fecha VARCHAR2, horaInicio VARCHAR2, horaFin VARCHAR2, estatus VARCHAR2, detalles VARCHAR2, cedulaPersonal NUMBER, idConsultorio NUMBER, 
   cedulaPaciente NUMBER, nombrePaciente VARCHAR2, apellidoPaciente VARCHAR2, telefonoPaciente NUMBER, fechaNacPac VARCHAR2, rutaFoto VARCHAR2)
  IS
  existePac NUMBER:= 0;
  idCita NUMBER;
  disponibleDoc BOOLEAN;
  noEstaDisp EXCEPTION;

      BEGIN
        disponibleDoc := "manejoCitasMedicas".estaDisponibleDoctor(cedulapersonal, '', horainicio, horafin);
        IF disponibleDoc = TRUE THEN
            INSERT INTO SISTEMACADE.CITA (idcita, fecha, horainicio, horafin, estatus, detalles, cedulapersonal, idconsultorio) 
            VALUES(SEQ_IDCITA.nextval, TO_DATE(fecha,'dd-mm-yy'), TO_TIMESTAMP(horaInicio,'HH24:MI'), TO_TIMESTAMP(horaFin,'HH24:MI'), estatus, detalles, cedulaPersonal, idConsultorio);

            SELECT SEQ_idCita.nextval INTO idCita FROM DUAL;
            idCita := idCita - 1;

            existePac := existePaciente(cedulaPaciente);
        
            IF existePac = 0 THEN
                DBMS_OUTPUT.put_line('El Paciente no esta registrado en el sistema');
                INSERT INTO SISTEMACADE.PACIENTE(cedulaPaciente, datosPersonales, habilitado) 
                VALUES (cedulaPaciente, datosPersonales(nombrePaciente,apellidoPaciente,telefonoPaciente,to_date(fechaNacPac,'dd/mm/yy'),bfilename('PACIENTES',rutaFoto)), 1);
            
                INSERT INTO SISTEMACADE.HISTORIAL_PACIENTE(idHistorial, idCita, numeroSerialFarmaco, numeroSerialEm, idEmergencia, cedulaPaciente, detalles) 
                VALUES (SEQ_idHistorial.nextval, idCita, null, null, null, cedulaPaciente, '');
            END IF;
          
            IF existePac = 1 THEN
                DBMS_OUTPUT.put_line('El Paciente ya esta registrado en el sistema');
                INSERT INTO SISTEMACADE.HISTORIAL_PACIENTE(idHistorial, idCita, numeroSerialFarmaco, numeroSerialEm, idEmergencia, cedulaPaciente, detalles) 
                VALUES (SEQ_idHistorial.nextval, idCita, null, null, null, cedulaPaciente, '');
            END IF;
        ELSE raise noEstaDisp;
        END IF;
        EXCEPTION 
            WHEN noEstaDisp THEN
                DBMS_OUTPUT.put_line('El doctor ingresado no esta disponible en el horario ingresado');
            WHEN OTHERS THEN
                DBMS_OUTPUT.put_line('Error insertando los datos');
        
      END;

    FUNCTION existeEM
    (serial NUMBER)
    RETURN BOOLEAN IS
    cantidad NUMBER:= 0;
    BEGIN
        SELECT COUNT(numeroSerialEM) INTO cantidad FROM equipo_medico
        WHERE numeroSerialEM = serial;
        IF cantidad > 0 THEN
        RETURN TRUE;
        ELSE
        RETURN FALSE;
        END IF;
        EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Los datos no existen o estan corruptos');
        WHEN OTHERS THEN 
            DBMS_OUTPUT.PUT_LINE('Ocurrio un error durante la operacion');
    END; 

    FUNCTION existeFAR
    (serial NUMBER)
    RETURN BOOLEAN IS
    cantidad NUMBER:= 0;
    BEGIN
        SELECT COUNT(numeroSerialFarmaco) INTO cantidad FROM farmaco
        WHERE numeroSerialFarmaco = serial;
        IF cantidad > 0 THEN
        RETURN TRUE;
        ELSE
        RETURN FALSE;
        END IF;
        EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Los datos no existen o estan corruptos');
        WHEN OTHERS THEN 
            DBMS_OUTPUT.PUT_LINE('Ocurrio un error durante la operacion');
    END; 

  PROCEDURE registrarHistorialPaciente
  (idCita NUMBER, numeroSerialFarmaco NUMBER, numeroSerialEm NUMBER, cedulaPaciente NUMBER, detalles VARCHAR2, porcentajeUso NUMBER) 
  IS
  existeEM BOOLEAN;
  existeFAR BOOLEAN;
  noExiste EXCEPTION;
  numFar NUMBER := 0;
  numEm NUMBER := 0;
  existePac NUMBER;
  noExistePac EXCEPTION;

      BEGIN
        IF numeroSerialFarmaco = NULL THEN 
            existeFAR := "manejoCitasMedicas".existeFAR(numFar);
        ELSE
            existeFAR := "manejoCitasMedicas".existeFAR(numeroSerialFarmaco);
        END IF;

        IF numeroSerialEm = NULL THEN 
            existeEM := "manejoCitasMedicas".existeEM(numEm);
        ELSE
            existeEM := "manejoCitasMedicas".existeEM(numeroSerialEm);
        END IF;
        
        existePac := existePaciente(cedulaPaciente);

        IF (existeEM = TRUE OR existeFAR = TRUE) AND existePac = 1 THEN
            INSERT INTO SISTEMACADE.HISTORIAL_PACIENTE(idHistorial, idCita, numeroSerialFarmaco, numeroSerialEm, idEmergencia, cedulaPaciente, detalles) 
            VALUES (SEQ_idHistorial.nextval, idCita, numeroSerialFarmaco, numeroSerialEm, null, cedulaPaciente, detalles);
        ELSE IF existePac = 0 THEN
            raise noExistePac;
        END IF;
        END IF;
        IF existeEM = TRUE THEN
            INSERT INTO SISTEMACADE.EQUIPO_MEDICO_CITA(numeroSerialEm, idCita, porcentajeUso) VALUES (numeroSerialEm, idCita, porcentajeUso);
        ELSE raise noExiste;
        END IF;
        EXCEPTION 
            WHEN noExiste THEN
                DBMS_OUTPUT.put_line('Los valores de los farmacos o equipo medico que esta tratando de ingresar no existen');
            WHEN noExistePac THEN
                DBMS_OUTPUT.put_line('El paciente ingresado no existe');
            WHEN OTHERS THEN
                DBMS_OUTPUT.put_line('Ocurrio un error durante la operacion');
      END;

      FUNCTION estaDisponibleDoctor
      (cedula NUMBER, dia VARCHAR2, horaInicio VARCHAR2, horaFin VARCHAR2)
      RETURN BOOLEAN 
      IS
      cuenta NUMBER;

      BEGIN
        SELECT COUNT(idhorario) INTO cuenta FROM HORARIO
        WHERE horainicio <= horaInicio
        AND horafin >= horaFin;
        IF cuenta > 0 THEN
            RETURN TRUE;
        ELSE
            RETURN FALSE;
        END IF;
      END;

FUNCTION existeTransporte
    (matricula VARCHAR2)
    RETURN BOOLEAN IS
    cantidad NUMBER:= 0;
    BEGIN
        SELECT COUNT(matricula) INTO cantidad FROM transporte
        WHERE matricula = matricula;
        IF cantidad > 0 THEN
        RETURN TRUE;
        ELSE
        RETURN FALSE;
        END IF;
        EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Los datos no existen o estan corruptos');
        WHEN OTHERS THEN 
            DBMS_OUTPUT.PUT_LINE('Ocurrio un error durante la operacion');
    END; 

    PROCEDURE realizarTraslado
    (fecha VARCHAR2, hora VARCHAR2, matricula VARCHAR2) 
    IS
    existe BOOLEAN;
    noExiste EXCEPTION;

    BEGIN
        existe := existeTransporte(matricula);
        IF existe = TRUE THEN
        INSERT INTO SISTEMACADE.TRASLADO(idTraslado, fecha, hora, matricula) VALUES (SEQ_idTraslado.nextval, TO_DATE(fecha,'DD/MM/YY'), TO_TIMESTAMP(hora,'HH24:MI'), matricula);
        ELSE
        raise noExiste;
        END IF;
        EXCEPTION
        WHEN noExiste THEN
        DBMS_OUTPUT.PUT_LINE('La matricula introducida no existe');
    END;

    FUNCTION generarFacturaPaciente
    RETURN NUMBER IS
    idFactura FACTURA_PACIENTE.idfacturapaciente%TYPE;
    BEGIN
        SELECT SEQ_IDFACTURAPACIENTE.nextval INTO idFactura FROM DUAL;
        INSERT INTO SISTEMACADE.FACTURA_PACIENTE(IDFACTURAPACIENTE, FECHA, MONTO) 
        VALUES(idFactura, SYSDATE, 0);
        RETURN idFactura;
        EXCEPTION 
        WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Los datos no existen o estan corruptos');
        WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('Ocurrio un error durante la operacion');
    END;

    PROCEDURE registrarServicio (idCita NUMBER, idFactura NUMBER, precio FLOAT, descripcion VARCHAR2) 
    IS
    montoFact FLOAT;
    montoTotal FLOAT;
    BEGIN
        INSERT INTO SISTEMACADE.SERVICIO(IDSERVICIO, DESCRIPCION, PRECIO, IDCITA, IDEMERGENCIA, IDFACTURAPACIENTE) 
        VALUES(SEQ_idServicio.nextval, descripcion, precio, idCita, NULL, idFactura);
        SELECT monto INTO montoFact FROM factura_paciente WHERE idfacturapaciente = idFactura;
        montoTotal := montoFact + precio;
        UPDATE FACTURA_PACIENTE SET monto = montoTotal WHERE idfacturapaciente = idFactura;
        DBMS_OUTPUT.PUT_LINE('El servicio se ha registrado con exito');
        EXCEPTION 
        WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Los datos no existen o estan corruptos');
        WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('Ocurrio un error durante la operacion');
    END;

    PROCEDURE pacienteInscrito (cedula NUMBER)
    IS
    inscrito NUMBER;

    BEGIN
        inscrito := existePaciente(cedula);
        if inscrito = 1 then
            DBMS_OUTPUT.PUT_LINE('El paciente esta registrado en el sistema');
        else
            DBMS_OUTPUT.PUT_LINE('El paciente no esta registrado en el sistema');
        end if;
    END;

    FUNCTION existePlan (id NUMBER)
    RETURN NUMBER
    IS
    existe NUMBER;

    BEGIN
        select count(p.idplan) INTO existe
        from plan p
        where p.idplan = id;
        RETURN existe;
     --   if existe = 1 then
     --       RETURN true;
     --   else RETURN false;
     --   end if;

    END;

    FUNCTION poseePlan (cedula NUMBER)
    RETURN BOOLEAN
    IS
    posee NUMBER;

    BEGIN
        select count(hpp.cedulapaciente) INTO posee
        from paciente pa, plan p, historial_plan_paciente hpp
        where pa.cedulapaciente = hpp.cedulapaciente
        and p.idplan = hpp.idplan
        and hpp.cedulapaciente = cedula
        and hpp.fechafin is null;
            
        if posee = 1 then
            RETURN TRUE;
        else
            RETURN FALSE;
        end if;
    END;

    PROCEDURE asignarPlanPaciente (cedula NUMBER, idPlan NUMBER, motivo VARCHAR2)
    IS
    posee BOOLEAN;
    existePac NUMBER;
    existePla NUMBER;
    noExistePac EXCEPTION;
    noExistePlan EXCEPTION;

    BEGIN
        posee := poseePlan (cedula);
        existePac := existePaciente (cedula);
        existePla := existePlan (idPlan);
        if existePac = 0 then
           raise noExistePac;
        end if;
        if existePla = 0 then
            raise noExistePlan;
        end if;
        if posee = true then
            UPDATE HISTORIAL_PLAN_PACIENTE 
            SET fechaFin = SYSDATE
            WHERE cedulaPaciente = cedula;

            INSERT INTO HISTORIAL_PLAN_PACIENTE(cedulaPaciente, idPlan, fechaInicio, fechaFin, motivoCambio) 
            VALUES (cedula, idPlan, SYSDATE, NULL, motivo);        
        end if;

        if posee = false then
            INSERT INTO HISTORIAL_PLAN_PACIENTE(cedulaPaciente, idPlan, fechaInicio, fechaFin, motivoCambio) 
            VALUES (cedula, idPlan, SYSDATE, NULL, motivo);
        end if;

        EXCEPTION
        WHEN noExistePac THEN
            DBMS_OUTPUT.PUT_LINE('El paciente ingresado no existe');
        WHEN noExistePlan THEN
            DBMS_OUTPUT.PUT_LINE('El plan ingresado no existe');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Ocurrio un error durante la operacion');

    END;

    PROCEDURE redireccionarCita (id NUMBER)
    IS
    personal NUMBER;
    espec VARCHAR2(100);
    fech DATE;
    hi TIMESTAMP;
    hf TIMESTAMP;
    cons NUMBER;
    maximo NUMBER;

    BEGIN
        select pm.cedulapersonal, pm.especialidad, c.fecha, c.horaInicio, c.horaFin, c.idConsultorio INTO personal, espec, fech, hi, hf, cons
        from cita c, personal_medico pm, historial_paciente hp, paciente p
        where c.cedulapersonal = pm.cedulapersonal
        and hp.idcita = c.idcita
        and hp.cedulapaciente = p.cedulapaciente
        and c.idcita = id;

        update cita c set c.estatus = 'A'
        where c.idcita = id;

        select MAX(pm.cedulapersonal) INTO maximo
        from personal_medico pm
        where pm.cedulapersonal <> personal
        and pm.especialidad = espec;
 
        insert into cita(idCita, fecha, horaInicio, horaFin, estatus, detalles, cedulaPersonal, idConsultorio)
        values (SEQ_idCita.nextval, fech, hi, hf, 'C', 'Redireccionamiento de cita', maximo, cons);

		DBMS_OUTPUT.PUT_LINE('La cita ha sido redireccionada exitosamente');
		
    END;

END;